package tech.yozo.bamboo.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import java.io.File;
import java.util.ArrayList;
import java.util.List;

import tech.yozo.bamboo.model.Ebook;
import tech.yozo.bamboo.utils.Utils;

public class BookDbHelper extends SQLiteOpenHelper {
    // If you change the database schema, you must increment the database version.
    public static final int DB_VERSION = 1;
    public static final String DB_NAME = "ebook.db";
    public Context context;

    private static final String SQL_CREATE_ENTRIES =
            "CREATE TABLE " + BookEntry.TABLE_NAME + " (" +
                    BookEntry._ID + " INTEGER PRIMARY KEY," +
                    BookEntry.COLUMN_TITLE + " VARCHAR(90)," +
                    BookEntry.COLUMN_FILE + " VARCHAR(100)," +
                    BookEntry.COLUMN_FORMAT + " VARCHAR(10)," +
                    BookEntry.COLUMN_LANGUAGE + " VARCHAR(10)," +
                    BookEntry.COLUMN_COVER_IMAGE + " VARCHAR(100)," +
                    BookEntry.COLUMN_HASH + " VARCHAR(64) UNIQUE," +
                    BookEntry.COLUMN_PROGRESS + " INTEGER)";

    private static final String SQL_DELETE_ENTRIES =
            "DROP TABLE IF EXISTS " + BookEntry.TABLE_NAME;

    public BookDbHelper(Context context) {
        super(context, DB_NAME, null, DB_VERSION);
        this.context = context;
        File dbFile = context.getDatabasePath(DB_NAME);
        if (!dbFile.exists()) {
            Utils.copyFileFromAssets(context, "db/" + DB_NAME, dbFile.getPath());
        }
    }

    public void onCreate(SQLiteDatabase db) {
        db.execSQL(SQL_CREATE_ENTRIES);
    }

    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // This database is only a cache for online data, so its upgrade policy is
        // to simply to discard the data and start over
        db.execSQL(SQL_DELETE_ENTRIES);
        onCreate(db);
    }

    public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        onUpgrade(db, oldVersion, newVersion);
    }

    public List<Ebook> getAllBooks() {
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor = db.query(BookEntry.TABLE_NAME,
                null,
                null,
                null,
                null,
                null,
                null);

        List<Ebook> ebooks = new ArrayList<>();
        while (cursor.moveToNext()) {
            Ebook ebook = new Ebook(
                    cursor.getString(cursor.getColumnIndexOrThrow(BookEntry.COLUMN_TITLE)),
                    cursor.getString(cursor.getColumnIndexOrThrow(BookEntry.COLUMN_FILE)),
                    cursor.getString(cursor.getColumnIndexOrThrow(BookEntry.COLUMN_FORMAT)),
                    cursor.getString(cursor.getColumnIndexOrThrow(BookEntry.COLUMN_LANGUAGE)),
                    cursor.getString(cursor.getColumnIndexOrThrow(BookEntry.COLUMN_COVER_IMAGE)),
                    cursor.getString(cursor.getColumnIndexOrThrow(BookEntry.COLUMN_HASH)),
                    cursor.getInt(cursor.getColumnIndexOrThrow(BookEntry.COLUMN_PROGRESS)));
            ebooks.add(ebook);
        }
        cursor.close();
        db.close();
        return ebooks;
    }

    public void saveBook(Ebook ebook) {
        ContentValues values = new ContentValues();
        values.put(BookEntry.COLUMN_TITLE, ebook.title);
        values.put(BookEntry.COLUMN_FILE, ebook.file);
        values.put(BookEntry.COLUMN_FORMAT, ebook.format);
        values.put(BookEntry.COLUMN_LANGUAGE, ebook.language);
        values.put(BookEntry.COLUMN_COVER_IMAGE, ebook.coverImage);
        values.put(BookEntry.COLUMN_HASH, ebook.hash);
        values.put(BookEntry.COLUMN_PROGRESS, ebook.progress);

        SQLiteDatabase db = getWritableDatabase();
        Cursor cursor = db.query(BookEntry.TABLE_NAME,
                null,
                BookEntry.COLUMN_HASH + "=?",
                new String[]{ebook.hash},
                null,
                null,
                null);
        if (cursor.moveToFirst()) {
            db.update(BookEntry.TABLE_NAME, values, BookEntry.COLUMN_HASH + "=?", new String[]{ebook.hash});
        } else {
            db.insert(BookEntry.TABLE_NAME, null, values);
        }
        db.close();
    }

    public void saveBooks(List<Ebook> ebooks) {
        SQLiteDatabase db = getWritableDatabase();
        db.beginTransaction();

        for (Ebook ebook : ebooks) {
            ContentValues values = new ContentValues();
            values.put(BookEntry.COLUMN_TITLE, ebook.title);
            values.put(BookEntry.COLUMN_FILE, ebook.file);
            values.put(BookEntry.COLUMN_FORMAT, ebook.format);
            values.put(BookEntry.COLUMN_LANGUAGE, ebook.language);
            values.put(BookEntry.COLUMN_COVER_IMAGE, ebook.coverImage);
            values.put(BookEntry.COLUMN_HASH, ebook.hash);
            values.put(BookEntry.COLUMN_PROGRESS, ebook.progress);

            Cursor cursor = db.query(BookEntry.TABLE_NAME,
                    null,
                    BookEntry.COLUMN_HASH + "=?",
                    new String[]{ebook.hash},
                    null,
                    null,
                    null);
            if (cursor.moveToFirst()) {
                db.update(BookEntry.TABLE_NAME, values, BookEntry.COLUMN_HASH + "=?", new String[]{ebook.hash});
            } else {
                db.insert(BookEntry.TABLE_NAME, null, values);
            }
            cursor.close();
        }

        db.setTransactionSuccessful();
        db.endTransaction();
        db.close();
    }
}